package db;
import beans.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDao extends ConnBean{
    private Connection connection=null;
    public List<Book> getAllRecords(){
        ResultSet rs=null;
        Statement stmt=null;
        ArrayList <Book> list=new ArrayList<Book>();
        try {
            //��ȡ���ݿ�����(url,username,password)
            connection = getConnection();
            if(connection==null)
                System.out.println("error");
            //׼��ִ�����ݿ��ѯ
            String sql="select * from Book";
            //׼��SQLִ��;
            stmt =connection.createStatement();
            //ִ�����ݿ��ѯ����ȡ�����
            rs=stmt.executeQuery(sql);
            while(rs.next()){
                Book book=new Book();
                book.setBook_id(rs.getInt(1));
                book.setBook_name(rs.getString(2));
                book.setBook_press(rs.getString(3));
                book.setBook_author(rs.getString(4));
                book.setBook_price(rs.getInt(5));
                list.add(book);
            }
            //�رձ��ʽ����
            stmt.close();
            //�ر����ݿ�
            connection.close();

        }catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try{
                if (connection!=null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        return list;
    }

    public Book getRecordOne(int id){
        ResultSet rs=null;
        Statement stmt=null;
        Book user=null;
        try {
            //��ȡ���ݿ�����(url,username,password)
            connection = getConnection();
            if(connection==null)
                System.out.println("error");
            //׼��ִ�����ݿ��ѯ
            String sql="select * from book where book_id='"+id+"'";
            //׼��SQLִ��;
            stmt =connection.createStatement();
            //ִ�����ݿ��ѯ����ȡ�����
            rs=stmt.executeQuery(sql);
            if(rs!=null && rs.next()){//ִ����sql����rsָ���һ�������ǰ�棬���Ա���ִ��һ��next()����
                System.out.println("right");
                user=new Book();
                user.setBook_id(rs.getInt(1));
                user.setBook_name(rs.getString(2));
                user.setBook_press(rs.getString(3));
                user.setBook_author(rs.getString(4));
                user.setBook_price(rs.getInt(5));
            }
            else
            {
                System.out.println("null");
                user=null;
            }
            //�رձ��ʽ����
            stmt.close();
            //�ر����ݿ�
            connection.close();
            //return user;
        }catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try{
                if (connection!=null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        return user;
    }


    public List<Book> getRecord(String name){
    ResultSet rs=null;
    Statement stmt=null;
        Book book=null;
    ArrayList <Book> list=new ArrayList<Book>();
        try {
            //��ȡ���ݿ�����(url,username,password)
            connection = getConnection();
            if(connection==null)
                System.out.println("error");
            //׼��ִ�����ݿ��ѯ
            String sql="select * from book where book_name like '%"+name+"%'";
            //׼��SQLִ��;
            stmt =connection.createStatement();
            //ִ�����ݿ��ѯ����ȡ�����
            rs=stmt.executeQuery(sql);
            while(rs.next()){//ִ����sql����rsָ���һ�������ǰ�棬���Ա���ִ��һ��next()����
                System.out.println("right");
                    book=new Book();
                    book.setBook_id(rs.getInt(1));
                    book.setBook_name(rs.getString(2));
                    book.setBook_press(rs.getString(3));
                    book.setBook_author(rs.getString(4));
                    book.setBook_price(rs.getInt(5));
                    list.add(book);
            }
            System.out.println("123   "+list.size());
//            else
//            {
//                System.out.println("null");
//                list=null;
//            }
            //�رձ��ʽ����
            stmt.close();
            //�ر����ݿ�
            connection.close();
            //return user;
        }catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try{
                if (connection!=null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        return list;
    }

    public boolean insertRecord(Book record)
    {
        PreparedStatement pstmt=null;
        String sql="insert into Book values(?,?,?,?,?)";
        if(record==null)
            return false;
        boolean result=false;
        int number=0;
        try{
            connection=getConnection();
/*			System.out.println(record.getId());
			System.out.println( record.getAccount());
			System.out.println(record.getPassword());
			System.out.println(record.getType());
			System.out.println(record.getName());	*/

            pstmt=connection.prepareStatement(sql);
            pstmt.setInt(1, record.getBook_id());
            pstmt.setString(2, record.getBook_name());
            pstmt.setString(3, record.getBook_press());
            pstmt.setString(4, record.getBook_author());
            pstmt.setInt(5, record.getBook_price());

            number=pstmt.executeUpdate();
            //�رձ��ʽ����
            pstmt.close();
            //�ر����ݿ�
            connection.close();
        }catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try{
                if (connection!=null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        //System.out.println("***"+number);
        if(number>0)
            result=true;
        return result;
    }

    public boolean deleteRecord(Book record)
    {
        PreparedStatement pstmt=null;
        String sql="delete from Book where book_id =?";

        if(record==null)
            return false;
        int result=0;
        try{
            connection=getConnection();
            pstmt=connection.prepareStatement(sql);
            pstmt.setInt(1, record.getBook_id());
            result=pstmt.executeUpdate();

            System.out.println(record.getBook_name());
            //�رձ��ʽ����
            pstmt.close();
            //�ر����ݿ�
            connection.close();
        }catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try{
                if (connection!=null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        if(result>0)
            return true;
        else
            return false;
    }

    public boolean updateRecord(Book beforeRecord,Book afterRecord)
    {
        if(beforeRecord==null || afterRecord==null)
            return false;
        PreparedStatement pstmt=null;
        String sql="update book set book_id = ?, book_name  = ?, book_press = ?, book_author = ?,book_price =? "+"where book_id="+beforeRecord.getBook_id();
        int result=0;
        try{
            connection=getConnection();
            pstmt=connection.prepareStatement(sql);
            pstmt.setInt(1, afterRecord.getBook_id());
            pstmt.setString(2, afterRecord.getBook_name());
            pstmt.setString(3, afterRecord.getBook_press());
            pstmt.setString(4, afterRecord.getBook_author());
            pstmt.setInt(5, afterRecord.getBook_price());

            result=pstmt.executeUpdate();
            //�رձ��ʽ����
            pstmt.close();
            //�ر����ݿ�
            connection.close();
        }catch (SQLException e) {
            e.printStackTrace();
        } finally{
            try{
                if (connection!=null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        if(result>0)
            return true;
        else
            return false;
    }

}
